package com.sservice.web.controller.admin;

import com.sservice.model.dao.AdvanceReceiptMapper;
import com.sservice.model.dao.ClienteleReconciliationMapper;
import com.sservice.model.dao.IClienteleDao;
import com.sservice.model.vo.AdvanceReceiptVo;
import com.sservice.model.vo.ClienteleReconciliationVo;
import com.sservice.service.service.ClienteleReconciliationService;
import com.sservice.service.service.ExportService;
import com.sservice.service.service.IClienteleService;
import com.sservice.base.entity.dto.BaseResult;
import com.sservice.base.entity.dto.ListResult;
import com.sservice.base.entity.dto.Pagination;
import com.sservice.model.entity.ClienteleReconciliation;
import com.sservice.web.common.Utils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by suweiming on 2017/12/20.
 */

@Controller
@RequestMapping("/admin/clienteleReconciliation")
public class ClienteleReconciliationController {
    @Autowired
    ClienteleReconciliationService service;
    @Autowired
    ClienteleReconciliationMapper mapper;
    @Autowired
    IClienteleService clientService;
    @Autowired
    IClienteleDao clienteleDao;
    @Autowired
    AdvanceReceiptMapper advanceReceiptMapper;
    @Autowired
    ExportService exportService;
    final static String Date1970 = "1970-01-01";

    public AdvanceReceiptVo parseType(AdvanceReceiptVo ar, String type) {
        if (ar.getType() != null) {
            String[] tye = null;
            tye = ar.getType().split(",");
            for (int i = 0; i < tye.length; i++) {
                if (tye[i].equals("")) {
                    ar.setTypeAll(true);
                }
                if (tye[i].equals("0")) {
                    ar.setTypeAR(true);
                }
                if (tye[i].equals("8")) {
                    if (type.equals("order")) {
                        ar.setTypeOrder(true);
                    } else {
                        ar.setTypeBill(true);
                    }
                }
                if (tye[i].equals("18")) {
                    ar.setTypeOrderBack(true);
                }
                if (tye[i].equals("9")) {
                    ar.setTypeApplyPay(true);
                }
            }
        } else {
            ar.setTypeAll(true);
        }
        return ar;
    }

    public ClienteleReconciliationVo parseType(ClienteleReconciliationVo ar, String type) {
        if (ar.getType() != null) {
            String[] tye = null;
            tye = ar.getType().split(",");
            for (int i = 0; i < tye.length; i++) {
                if (tye[i].equals("")) {
                    ar.setTypeAll(true);
                }
                if (tye[i].equals("0")) {
                    ar.setTypeAR(true);
                }
                if (tye[i].equals("9")) {
                    ar.setTypeApplyPay(true);
                }
                if (tye[i].equals("8")) {
                    if (type.equals("order")) {
                        ar.setTypeOrder(true);
                    } else {
                        ar.setTypeBill(true);
                    }

                }
                if (tye[i].equals("18")) {
                    ar.setTypeOrderBack(true);
                }
            }
        } else {
            ar.setTypeAll(true);
        }
        return ar;
    }

    /***
     * 分页查询
     * @param arg
     * @return
     */
    @ResponseBody
    @RequestMapping(value = "/querySale", method = RequestMethod.GET)
    public ListResult query(ClienteleReconciliationVo arg) {
        Integer total = mapper.countSale(arg);
        Pagination page = new Pagination(arg.getPage(), total, arg.getSize());
        ListResult res = new ListResult(true, mapper.querySale(arg));
        res.setPage(page);
        return res;
    }

    @RequestMapping(value = "/exportSale", method = RequestMethod.GET)
    public OutputStream exportSale(ClienteleReconciliationVo arg, HttpServletResponse response, HttpServletRequest request) {
        arg.setSize(Integer.MAX_VALUE);
        arg.setPage(0);
        List<ClienteleReconciliation> list = mapper.querySale(arg);
        arg.setMonthEnd(Utils.getYesterdayDate(arg.getMonthStart()));
        arg.setMonthStart(Date1970);
        List<ClienteleReconciliation> listPre = mapper.querySale(arg);
        Map<String, Object> listMap = Utils.convertListToMap(listPre, ClienteleReconciliation.class, "unitCode");
        ServletOutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }
        exportService.setResponseHeader(response, request, "客户往来对账总表Excel");
        String hql = "客户往来对账";
        List<Map> lm = new ArrayList<Map>();
        for (int i = 0; i < list.size(); i++) {
            Map map = new LinkedHashMap();
            ClienteleReconciliation ov = list.get(i);
            System.out.println(i);
            ov = initCR(ov);
            map.put("客户名称", ov.getClienteleName());
            map.put("统一信用代码", ov.getUnitCode());
            Object obj = listMap.get(ov.getUnitCode());
            ClienteleReconciliation ovPre = null;
            if (obj != null) {
                ovPre = (ClienteleReconciliation) obj;

            }
            if (ovPre != null) {
                ovPre = initCR(ovPre);
                map.put("[订单]前期应收余额", BigDecimal.valueOf(0.0).subtract(getOrderContainStageTotal(ovPre)));
            } else {
                map.put("[订单]前期应收余额", BigDecimal.valueOf(0.0));
            }

            map.put("[订单]本期应收", BigDecimal.valueOf(ov.getOrderReceivable()).subtract(BigDecimal.valueOf(ov.getOrderPayable())));
            map.put("[订单]本期实收", BigDecimal.valueOf(ov.getOrderReceipts()).subtract(BigDecimal.valueOf(ov.getOrderNetPay())));

            if (ovPre != null) {
                map.put("[订单]应收余额", BigDecimal.valueOf(0.0).subtract(getOrderContainStageTotal(ov).add(getOrderContainStageTotal(ovPre))));
                map.put("[收货单]前期应收余额", BigDecimal.valueOf(0.0).subtract(getBillContainStageTotal(ovPre)));
            } else {
                map.put("[订单]应收余额", BigDecimal.valueOf(0.0).subtract(getOrderContainStageTotal(ov)));
                map.put("[发货单]前期应收余额", 0.0);
            }
            map.put("[发货单]本期应收", BigDecimal.valueOf(ov.getBillReceivable()).subtract(BigDecimal.valueOf(ov.getBillPayable())));
            map.put("[发货单]本期实收", BigDecimal.valueOf(ov.getBillReceipts()).subtract(BigDecimal.valueOf(ov.getBillNetPay())));
            if (ovPre != null) {
                map.put("[发货单]本期应收余额", BigDecimal.valueOf(0.0).subtract(getBillContainStageTotal(ov).add(getBillContainStageTotal(ovPre))));
            } else {
                map.put("[发货单]本期应收余额", BigDecimal.valueOf(0.0).subtract(getBillContainStageTotal(ov)));
            }
            lm.add(map);
        }
        return exportService.exportExcel(hql, lm, outputStream);
    }

    ClienteleReconciliation initCR(ClienteleReconciliation cr) {
        if (cr.getOrderNetPay() == null) {
            cr.setOrderNetPay(0.0);
        }
        if (cr.getOrderPayable() == null) {
            cr.setOrderPayable(0.0);
        }
        if (cr.getOrderReceipts() == null) {
            cr.setOrderReceipts(0.0);
        }
        if (cr.getOrderReceivable() == null) {
            cr.setOrderReceivable(0.0);
        }
        if (cr.getBillNetPay() == null) {
            cr.setBillNetPay(0.0);
        }
        if (cr.getBillPayable() == null) {
            cr.setBillPayable(0.0);
        }
        if (cr.getBillReceipts() == null) {
            cr.setBillReceipts(0.0);
        }
        if (cr.getBillReceivable() == null) {
            cr.setBillReceivable(0.0);
        }
        return cr;
    }


    /***
     * 基于客户订单的某个时间区间内往来对账
     * @param arg
     * @return
     */
    @ResponseBody
    @RequestMapping(value = "/querySaleByUnitCode", method = RequestMethod.GET)
    public BaseResult querySaleByUnitCode(ClienteleReconciliationVo arg) {
        arg = parseType(arg, "order");
        return new BaseResult(true, mapper.querySaleByUnitCode(arg));
    }

    @ResponseBody
    @RequestMapping(value = "/querySaleOrder", method = RequestMethod.GET)
    public ListResult querySaleOrder(AdvanceReceiptVo arg) {
        arg = parseType(arg, "order");
        int total = mapper.countSaleOrder(arg);
        Pagination page = new Pagination(arg.getPage(), total, arg.getSize());
        ListResult res = new ListResult(true, mapper.querySaleOrder(arg));
        if (arg.getPage() > 1) {
            List<AdvanceReceiptVo> arList = mapper.querySaleOrderBeforePage(arg);
            res.setBeforePage(arList);
        }
        res.setPage(page);
        return res;
    }

    /***
     *  客户往来对账 按订单导出
     * @param arg
     * @return
     */
    @RequestMapping(value = "/exportSaleOrder", method = RequestMethod.GET)
    public OutputStream exportSaleOrder(AdvanceReceiptVo arg, HttpServletRequest request, HttpServletResponse response) {
        arg = parseType(arg, "order");
        arg.setPage(0);
        arg.setSize(Integer.MAX_VALUE);
        List<AdvanceReceiptVo> list = mapper.querySaleOrder(arg);
        ServletOutputStream outputStream = null;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        try {
            outputStream = response.getOutputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }
        exportService.setResponseHeader(response, request, "客户订单往来对账Excel");
        String hql = "客户订单往来对账";

        List<Map> lm = new ArrayList<Map>();

        BigDecimal remain = BigDecimal.valueOf(0.0);
        BigDecimal needPayTotal = BigDecimal.valueOf(0.0); // 应付
        BigDecimal actualPayTotal = BigDecimal.valueOf(0.0); // 实付
        //按订单前期应付余额
        ClienteleReconciliationVo arPre = new ClienteleReconciliationVo();
        arPre.setUnitCode(arg.getUnitCode());
        arPre.setMonthStart(Date1970); //

        arPre.setMonthEnd(Utils.getYesterdayDate(arg.getMonthStart()));
        arPre = parseType(arPre, "order");
        ClienteleReconciliation arPreRes = mapper.querySaleByUnitCode(arPre);
        Map mapPre = new LinkedHashMap();
        mapPre.put("日期", null);
        mapPre.put("单据类型", "前期应付余额");
        mapPre.put("单号", null);
        mapPre.put("产品名称", null);
        mapPre.put("数量", null);
        mapPre.put("实际数量", null);
        mapPre.put("单价", null);
        mapPre.put("计量单位", null);
        remain = remain.add(BigDecimal.valueOf(arPreRes.getOrderPayable()).subtract(BigDecimal.valueOf(arPreRes.getOrderReceipts())));
        mapPre.put("应收金额", remain);
        BigDecimal actualPayPre = BigDecimal.valueOf(arPreRes.getOrderNetPay()).subtract(BigDecimal.valueOf(arPreRes.getOrderReceivable()));
        remain = remain.subtract(actualPayPre);
        mapPre.put("实收金额", actualPayPre);
        mapPre.put("应收余额", remain);
        mapPre.put("备注说明", null);
        lm.add(mapPre);
        //
        for (int i = 0; i < list.size(); i++) {
            Map map = new LinkedHashMap();
            AdvanceReceiptVo ov = list.get(i);
            map.put("日期", sdf.format(ov.getCreateAt()));
            String type = ov.getType();
            map.put("单据类型", "0".equals(type) ? "预收款单" : ("8".equals(type) ? "销售订单" : ("18".equals(type) ? "销售退款单" : "")));
            map.put("单号", ov.getNo());
            map.put("产品名称", ov.getGoodsName());
            map.put("数量", ov.getNumber());
            map.put("实际数量", ov.getActual());
            map.put("单价", ov.getPrice());
            map.put("计量单位", ov.getUnit());
            BigDecimal needPay = "8".equals(type) ? ov.getTotalDeposit() : BigDecimal.valueOf(0.0);
            needPayTotal = needPayTotal.add(needPay);
            BigDecimal actualPay = "0".equals(type) ? ov.getTotalDeposit() : ("8".equals(type) ? BigDecimal.valueOf(0.0) : ("8".equals(type) ? BigDecimal.valueOf(0.0).subtract(ov.getTotalDeposit()) : BigDecimal.valueOf(0.0)));
            actualPayTotal = actualPayTotal.add(actualPay);
            remain = remain.add(needPay.subtract(actualPay));
            map.put("应收金额", needPay);
            map.put("实收金额", actualPay);
            map.put("应收余额", remain);
            map.put("备注说明", ov.getRemark());
            lm.add(map);
        }
        Map mapTotal = new LinkedHashMap();
        mapTotal.put("日期", null);
        mapTotal.put("单据类型", "本期应付余额");
        mapTotal.put("单号", null);
        mapTotal.put("产品名称", null);
        mapTotal.put("数量", null);
        mapTotal.put("实际数量", null);
        mapTotal.put("单价", null);
        mapTotal.put("计量单位", null);
        mapTotal.put("应收金额", needPayTotal);
        mapTotal.put("实收金额", actualPayTotal);
        mapTotal.put("应收余额", remain);
        mapTotal.put("备注说明", null);
        lm.add(mapTotal);

        return exportService.exportExcel(hql, lm, outputStream);
    }

    @ResponseBody
    @RequestMapping(value = "/querySaleBill", method = RequestMethod.GET)
    public ListResult querySaleBill(AdvanceReceiptVo arg) {
        arg = parseType(arg, "bill"); // bill
        int total = mapper.countSaleBill(arg);
        Pagination page = new Pagination(arg.getPage(), total, arg.getSize());
        ListResult res = new ListResult(true, mapper.querySaleBill(arg));
        if (arg.getPage() > 1) {
            List<AdvanceReceiptVo> arList = mapper.querySaleBillBeforePage(arg);
            res.setBeforePage(arList);
        }
        res.setPage(page);
        return res;
    }

    /***
     *   客户往来对账 按发货单导出
     * @param arg
     * @param request
     * @param response
     * @return
     */
    @RequestMapping(value = "/exportSaleBill", method = RequestMethod.GET)
    public OutputStream exportSaleBill(AdvanceReceiptVo arg, HttpServletRequest request, HttpServletResponse response) {
        arg = parseType(arg, "bill"); // bill
        arg.setPage(0);
        arg.setSize(Integer.MAX_VALUE);
        List<AdvanceReceiptVo> list = mapper.querySaleBill(arg);
        ServletOutputStream outputStream = null;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        try {
            outputStream = response.getOutputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }
        exportService.setResponseHeader(response, request, "客户发货单往来对账Excel");
        String hql = "客户发货单往来对账";

        List<Map> lm = new ArrayList<Map>();

        BigDecimal remain = BigDecimal.valueOf(0.0);
        BigDecimal needPayTotal = BigDecimal.valueOf(0.0); // 应付
        BigDecimal actualPayTotal = BigDecimal.valueOf(0.0); // 实付
        //按订单前期应付余额
        ClienteleReconciliationVo arPre = new ClienteleReconciliationVo();
        arPre.setUnitCode(arg.getUnitCode());
        arPre.setMonthStart(Date1970); //
        //
        arPre.setMonthEnd(Utils.getYesterdayDate(arg.getMonthStart()));
        arPre = parseType(arPre, "bill");
        ClienteleReconciliation arPreRes = mapper.querySaleByUnitCode(arPre);
        Map mapPre = new LinkedHashMap();
        mapPre.put("日期", null);
        mapPre.put("单据类型", "前期应付余额");
        mapPre.put("单号", null);
        mapPre.put("产品名称", null);
        mapPre.put("数量", null);
        mapPre.put("实际数量", null);
        mapPre.put("单价", null);
        remain = remain.add(BigDecimal.valueOf(arPreRes.getBillPayable()))
                .subtract(BigDecimal.valueOf(arPreRes.getBillNetPay()))
                .subtract(BigDecimal.valueOf(arPreRes.getBillReceivable()))
                .add(BigDecimal.valueOf(arPreRes.getBillReceipts()));
        remain = BigDecimal.valueOf(0.0).subtract(remain);//
        mapPre.put("应收金额", null);
//            BigDecimal actualPayPre = BigDecimal.valueOf(arPreRes.getBillNetPay()).subtract(BigDecimal.valueOf(arPreRes.getBillReceivable()));
//        remain = remain.subtract(actualPayPre);
        mapPre.put("实收金额", null);
        mapPre.put("应收余额", remain);
        mapPre.put("备注说明", null);
        lm.add(mapPre);
        //
        for (int i = 0; i < list.size(); i++) {
            Map map = new LinkedHashMap();
            AdvanceReceiptVo ov = list.get(i);
            map.put("日期", sdf.format(ov.getCreateAt()));
            String type = ov.getType();
            map.put("单据类型", "0".equals(type) ? "预收款单" : ("18".equals(type) ? "销售退款单" : "发货单"));
            map.put("单号", ov.getNo());
            map.put("产品名称", ov.getGoodsName());
            map.put("数量", ov.getNumber());
            map.put("实际数量", ov.getActual());
            map.put("单价", ov.getPrice());
            BigDecimal needPay = ov.getNo().indexOf("T")>-1?ov.getTotalDeposit():BigDecimal.valueOf(0.0);
            needPay = needPay==null?BigDecimal.ZERO:needPay;
            needPayTotal = needPayTotal.add(needPay);
            BigDecimal actualPay = ov.getNo().indexOf("T")>-1?BigDecimal.valueOf(0.0):(ov.getNo().indexOf("Y")>-1?ov.getTotalDeposit():(BigDecimal.valueOf(0.0).subtract(ov.getTotalDeposit())));;
            actualPay = actualPay==null?BigDecimal.ZERO:actualPay;
            actualPayTotal = actualPayTotal.add(actualPay);
            remain = remain.add(needPay.subtract(actualPay));
            map.put("应收金额", needPay);
            map.put("实收金额", actualPay);
            map.put("应收余额", remain);
            map.put("备注说明", ov.getRemark());
            lm.add(map);
        }
        Map mapTotal = new LinkedHashMap();
        mapTotal.put("日期", null);
        mapTotal.put("单据类型", "本期应付余额");
        mapTotal.put("单号", null);
        mapTotal.put("产品名称", null);
        mapTotal.put("数量", null);
        mapTotal.put("实际数量", null);
        mapTotal.put("单价", null);
        mapTotal.put("应收金额", needPayTotal);
        mapTotal.put("实收金额", actualPayTotal);
        mapTotal.put("应收余额", remain);
        mapTotal.put("备注说明", null);
        lm.add(mapTotal);

        return exportService.exportExcel(hql, lm, outputStream);
    }


//

    @ResponseBody
    @RequestMapping(value = "/queryProvider", method = RequestMethod.GET)
    public ListResult queryProvider(ClienteleReconciliationVo arg) {
        Integer total = mapper.countProvider(arg);
        Pagination page = new Pagination(arg.getPage(), total, arg.getSize());
        ListResult res = new ListResult(true, mapper.queryProvider(arg));
        res.setPage(page);
        return res;
    }

    /***
     * 供应商往来对账总表导出
     * @param arg
     * @return
     */
    @RequestMapping(value = "/exportProvider", method = RequestMethod.GET)
    public OutputStream exportProvider(ClienteleReconciliationVo arg, HttpServletRequest request, HttpServletResponse response) {
        arg.setSize(Integer.MAX_VALUE);
        arg.setPage(0); //
        List<ClienteleReconciliation> list = mapper.queryProvider(arg);
        arg.setMonthEnd(Utils.getYesterdayDate(arg.getMonthStart()));
        arg.setMonthStart(Date1970);
        List<ClienteleReconciliation> listPre = mapper.queryProvider(arg);
        Map<String, Object> listMap = Utils.convertListToMap(listPre, ClienteleReconciliation.class, "unitCode");
        ServletOutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }
        exportService.setResponseHeader(response, request, "供应商往来对账总表Excel");
        String hql = "供应商往来对账";

        List<Map> lm = new ArrayList<Map>();

        for (int i = 0; i < list.size(); i++) {
            Map map = new LinkedHashMap();
            ClienteleReconciliation ov = list.get(i);

            map.put("供应商名称", ov.getClienteleName());
            map.put("统一信用代码", ov.getUnitCode());
            Object obj = listMap.get(ov.getUnitCode());
            ClienteleReconciliation ovPre = null;
            if (obj != null) {
                ovPre = (ClienteleReconciliation) obj;
            }
            if (ovPre != null) {
                map.put("[订单]前期应付余额", getOrderContainStageTotal(ovPre));
            } else {
                map.put("[订单]前期应付余额", BigDecimal.valueOf(0.0));
            }

            map.put("[订单]本期应付", BigDecimal.valueOf(ov.getOrderPayable()).subtract(BigDecimal.valueOf(ov.getOrderReceivable())));
            map.put("[订单]本期实付", BigDecimal.valueOf(ov.getOrderNetPay()).subtract(BigDecimal.valueOf(ov.getOrderReceipts())));

            if (ovPre != null) {
                map.put("[订单]应付余额", getOrderContainStageTotal(ov).add(getOrderContainStageTotal(ovPre)));
                map.put("[收货单]前期应付余额", getBillContainStageTotal(ovPre));
            } else {
                map.put("[订单]应付余额", getOrderContainStageTotal(ov));
                map.put("[收货单]前期应付余额", 0.0);
            }
            map.put("[收货单]本期应付", BigDecimal.valueOf(ov.getBillPayable()).subtract(BigDecimal.valueOf(ov.getBillReceivable())));
            map.put("[收货单]本期实付", BigDecimal.valueOf(ov.getBillNetPay()).subtract(BigDecimal.valueOf(ov.getBillReceipts())));
            if (ovPre != null) {
                map.put("[收货单]本期应付余额", getBillContainStageTotal(ov).add(getBillContainStageTotal(ovPre)));

            } else {
                map.put("[收货单]本期应付余额", getBillContainStageTotal(ov));
            }
            lm.add(map);
        }
        return exportService.exportExcel(hql, lm, outputStream);
    }

    /**
     * 获取订单某期应付余额
     *
     * @param cr
     * @return
     */
    public BigDecimal getOrderContainStageTotal(ClienteleReconciliation cr) {
        return BigDecimal.valueOf(0.0)
                .subtract(
                        BigDecimal.valueOf(cr.getOrderReceivable() == null ? 0.0 : cr.getOrderReceivable())
                                .subtract(BigDecimal.valueOf(cr.getOrderReceipts() == null ? 0.0 : cr.getOrderReceipts()))
                                .subtract(BigDecimal.valueOf(cr.getOrderPayable() == null ? 0.0 : cr.getOrderPayable()))
                                .add(BigDecimal.valueOf(cr.getOrderNetPay() == null ? 0.0 : cr.getOrderNetPay()))
                );
    }

    /**
     * 获取收货单某期应付余额
     *
     * @param cr
     * @return
     */
    public BigDecimal getBillContainStageTotal(ClienteleReconciliation cr) {
        return BigDecimal.valueOf(0.0)
                .subtract(
                        BigDecimal.valueOf(cr.getBillReceivable())
                                .subtract(BigDecimal.valueOf(cr.getBillReceipts()))
                                .subtract(BigDecimal.valueOf(cr.getBillPayable() == null ? 0 : cr.getBillPayable()))
                                .add(BigDecimal.valueOf(cr.getOrderNetPay() == null ? 0 : cr.getBillNetPay()))
                );
    }

    /**
     * 查询某个供应商的
     *
     * @param arg
     * @return
     */
    @ResponseBody
    @RequestMapping(value = "/queryProviderByUnitCode", method = RequestMethod.GET)
    public BaseResult queryProviderByUnitCode(ClienteleReconciliationVo arg) {
        arg = parseType(arg, "order");
        return new BaseResult(true, mapper.queryProviderByUnitCode(arg));
    }

    @ResponseBody
    @RequestMapping(value = "/queryProviderOrder", method = RequestMethod.GET)
    public ListResult queryProviderOrder(AdvanceReceiptVo arg) {
        int total = mapper.countProviderOrder(arg);
        Pagination page = new Pagination(arg.getPage(), total, arg.getSize());
        ListResult res = new ListResult(true, mapper.queryProviderOrder(arg));
        if (arg.getPage() > 1) {
            List<AdvanceReceiptVo> arList = mapper.queryProviderOrderBeforePage(arg);
            res.setBeforePage(arList);
        }
        res.setPage(page);
        return res;

    }

    /***
     *  供应商往来对账 按订单
     * @param arg
     * @return
     */
    @RequestMapping(value = "/exportProviderOrder", method = RequestMethod.GET)
    public OutputStream exportProviderOrder(AdvanceReceiptVo arg, HttpServletRequest request, HttpServletResponse response) {
        arg.setPage(0);
        arg.setSize(Integer.MAX_VALUE);
        List<AdvanceReceiptVo> list = mapper.queryProviderOrder(arg);
        ServletOutputStream outputStream = null;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        try {
            outputStream = response.getOutputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }
        exportService.setResponseHeader(response, request, "供应商订单往来对账Excel");
        String hql = "供应商订单往来对账";

        List<Map> lm = new ArrayList<Map>();

        BigDecimal remain = BigDecimal.valueOf(0.0);
        BigDecimal needPayTotal = BigDecimal.valueOf(0.0); // 应付
        BigDecimal actualPayTotal = BigDecimal.valueOf(0.0); // 实付
        //按订单前期应付余额
        ClienteleReconciliationVo arPre = new ClienteleReconciliationVo();
        arPre.setUnitCode(arg.getUnitCode());
        arPre.setMonthStart(Date1970); //

        arPre.setMonthEnd(Utils.getYesterdayDate(arg.getMonthStart()));
        arPre = parseType(arPre, "order");
        ClienteleReconciliation arPreRes = mapper.queryProviderByUnitCode(arPre);
        Map mapPre = new LinkedHashMap();
        mapPre.put("日期", null);
        mapPre.put("单据类型", "前期应付余额");
        mapPre.put("单号", null);
        mapPre.put("产品名称", null);
        mapPre.put("数量", null);
        mapPre.put("实际数量", null);
        mapPre.put("单价", null);
        mapPre.put("计量单位", null);
        remain = remain.add(BigDecimal.valueOf(arPreRes.getOrderPayable()).subtract(BigDecimal.valueOf(arPreRes.getOrderReceipts())));
        mapPre.put("应付金额", remain);
        BigDecimal actualPayPre = BigDecimal.valueOf(arPreRes.getOrderNetPay()).subtract(BigDecimal.valueOf(arPreRes.getOrderReceivable()));
        remain = remain.subtract(actualPayPre);
        mapPre.put("实付金额", actualPayPre);
        mapPre.put("应付余额", remain);
        mapPre.put("备注说明", null);
        lm.add(mapPre);
        //
        for (int i = 0; i < list.size(); i++) {
            Map map = new LinkedHashMap();
            AdvanceReceiptVo ov = list.get(i);
            map.put("日期", sdf.format(ov.getCreateAt()));
            String type = ov.getType();
            map.put("单据类型", "2".equals(type) ? "预付款单" : ("8".equals(type) ? "采购订单" : ("9".equals(type) ? "采购付款申请单" : ("5".equals(type) ? "采购订单现金退款" : ""))));
            map.put("单号", ov.getNo());
            map.put("产品名称", ov.getGoodsName());
            map.put("数量", ov.getNumber());
            map.put("实际数量", ov.getActual());
            map.put("单价", ov.getPrice());
            map.put("计量单位", ov.getUnit());
            BigDecimal needPay = "2".equals(type) ? BigDecimal.valueOf(0.0) : ("9".equals(type) ? BigDecimal.valueOf(0.0) : ("5".equals(type) ? BigDecimal.valueOf(0.0) : ov.getTotalDeposit()));
            needPayTotal = needPayTotal.add(needPay);
            BigDecimal actualPay = "2".equals(type) ? ov.getTotalDeposit() : ("9".equals(type) ? ov.getTotalDeposit() : ("5".equals(type) ? BigDecimal.valueOf(0.0).subtract(ov.getTotalDeposit()) : BigDecimal.valueOf(0.0)));
            actualPayTotal = actualPayTotal.add(actualPay);
            remain = remain.add(needPay.subtract(actualPay));
            map.put("应付金额", needPay);
            map.put("实付金额", actualPay);
            map.put("应付余额", remain);
            map.put("备注说明", ov.getRemark());
            lm.add(map);
        }
        Map mapTotal = new LinkedHashMap();
        mapTotal.put("日期", null);
        mapTotal.put("单据类型", "本期应付余额");
        mapTotal.put("单号", null);
        mapTotal.put("产品名称", null);
        mapTotal.put("数量", null);
        mapTotal.put("实际数量", null);
        mapTotal.put("单价", null);
        mapTotal.put("计量单位", null);
        mapTotal.put("应付金额", needPayTotal);
        mapTotal.put("实付金额", actualPayTotal);
        mapTotal.put("应付余额", remain);
        mapTotal.put("备注说明", null);
        lm.add(mapTotal);

        return exportService.exportExcel(hql, lm, outputStream);
    }

    @ResponseBody
    @RequestMapping(value = "/queryProviderBill", method = RequestMethod.GET)
    public ListResult queryProviderBill(AdvanceReceiptVo arg) {
        arg = parseType(arg, "bill");
        int total = mapper.countProviderBill(arg);
        Pagination page = new Pagination(arg.getPage(), total, arg.getSize());
        ListResult res = new ListResult(true, mapper.queryProviderBill(arg));
        if (arg.getPage() > 1) {
            List<AdvanceReceiptVo> arList = mapper.queryProviderBillBeforePage(arg);
            res.setBeforePage(arList);
        }
        res.setPage(page);
        return res;
    }

    /***
     * 供应商按收货单 往来对账导出
     * @param arg
     * @return
     */
    @RequestMapping(value = "/exportProviderBill", method = RequestMethod.GET)
    public OutputStream exportProviderBill(AdvanceReceiptVo arg, HttpServletRequest request, HttpServletResponse response) {
        arg = parseType(arg, "bill");
        arg.setPage(0);
        arg.setSize(Integer.MAX_VALUE);
        List<AdvanceReceiptVo> list = mapper.queryProviderBill(arg);
        ServletOutputStream outputStream = null;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        try {
            outputStream = response.getOutputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }
        exportService.setResponseHeader(response, request, "供应商收货单往来对账Excel");
        String hql = "供应商收货单往来对账";

        List<Map> lm = new ArrayList<Map>();

        BigDecimal remain = BigDecimal.valueOf(0.0);
        BigDecimal needPayTotal = BigDecimal.valueOf(0.0); // 应付
        BigDecimal actualPayTotal = BigDecimal.valueOf(0.0); // 实付
        //按订单前期应付余额
        ClienteleReconciliationVo arPre = new ClienteleReconciliationVo();
        arPre.setUnitCode(arg.getUnitCode());
        arPre.setMonthStart(Date1970); //

        arPre.setMonthEnd(Utils.getYesterdayDate(arg.getMonthStart()));
        arPre = parseType(arPre, "bill");
        ClienteleReconciliation arPreRes = mapper.queryProviderByUnitCode(arPre);
        Map mapPre = new LinkedHashMap();
        mapPre.put("日期", null);
        mapPre.put("单据类型", "前期应付余额");
        mapPre.put("单号", null);
        mapPre.put("产品名称", null);
        mapPre.put("数量", null);
        mapPre.put("实际数量", null);
        mapPre.put("单价", null);
        remain = remain.add(BigDecimal.valueOf(arPreRes.getBillPayable()).subtract(BigDecimal.valueOf(arPreRes.getBillReceipts())));
        mapPre.put("应付金额", remain);
        BigDecimal actualPayPre = BigDecimal.valueOf(arPreRes.getBillNetPay()).subtract(BigDecimal.valueOf(arPreRes.getBillReceivable()));
        remain = remain.subtract(actualPayPre);
        mapPre.put("实付金额", actualPayPre);
        mapPre.put("应付余额", remain);
        mapPre.put("备注说明", null);
        lm.add(mapPre);
        //
        for (int i = 0; i < list.size(); i++) {
            Map map = new LinkedHashMap();
            AdvanceReceiptVo ov = list.get(i);
            map.put("日期", sdf.format(ov.getCreateAt()));
            String type = ov.getType();
            map.put("单据类型", "1".equals(type) ? "预付款单" : ("8".equals(type) ? "收货单" : ("21".equals(type) ? "采购付款申请单" : ("5".equals(type) ? "采购订单现金退款" : "收货单"))));
            map.put("单号", ov.getNo());
            map.put("产品名称", ov.getGoodsName());
            map.put("数量", "21".equals(type) ? "-" : ("8".equals(type) ? ov.getNumber() : ("1".equals(type) ? ov.getNumber() : ("-"))));
            map.put("实际数量", (!"21".equals(type)) ? ov.getActual() : (!"1".equals(type) ? ov.getActual() : "-"));
            map.put("单价", ov.getPrice());
            BigDecimal needPay = ("5".equals(type) ? BigDecimal.valueOf(0.0) : ("8".equals(type) ? ov.getTotalDeposit() : ("21".equals(type) ? BigDecimal.valueOf(0.0) : (!"1".equals(ov.getType()) ? BigDecimal.valueOf(0.0) : ov.getTotalDeposit()))));
            needPayTotal = needPayTotal.add(needPay);
            BigDecimal actualPay = "5".equals(type) ? BigDecimal.valueOf(0.0).subtract(ov.getTotalDeposit()) : ("8".equals(type) ? BigDecimal.valueOf(0.0) :
                    ("21".equals(type) ? ov.getTotalDeposit() : (!"2".equals(type) ? BigDecimal.valueOf(0.0) : ov.getTotalDeposit())));
            actualPayTotal = actualPayTotal.add(actualPay);
            remain = remain.add(needPay.subtract(actualPay));
            map.put("应付金额", needPay);
            map.put("实付金额", actualPay);
            map.put("应付余额", remain);
            map.put("备注说明", ov.getRemark());
            lm.add(map);
        }
        Map mapTotal = new LinkedHashMap();
        mapTotal.put("日期", null);
        mapTotal.put("单据类型", "本期应付余额");
        mapTotal.put("单号", null);
        mapTotal.put("产品名称", null);
        mapTotal.put("数量", null);
        mapTotal.put("实际数量", null);
        mapTotal.put("单价", null);
        mapTotal.put("应付金额", needPayTotal);
        mapTotal.put("实付金额", actualPayTotal);
        mapTotal.put("应付余额", remain);
        mapTotal.put("备注说明", null);
        lm.add(mapTotal);

        return exportService.exportExcel(hql, lm, outputStream);
    }

    @ResponseBody
    @RequestMapping(value = "/queryClienteleBill", method = RequestMethod.GET)
    public ListResult queryClienteleBill(AdvanceReceiptVo arg) {
        List<AdvanceReceiptVo> list = advanceReceiptMapper.queryClienteleBill(arg);
        int total = advanceReceiptMapper.countClienteleBill(arg);
        Pagination page = new Pagination(arg.getPage(), total, arg.getSize());
        ListResult res = new ListResult(true, list);
        res.setPage(page);
        return res;
    }
}
